package com.dkd.system.utils;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;

public class ExcelHeaderReplacer {

    // 替换数据的映射表
    private static final Map<String, String> REPLACEMENT_MAP = new HashMap<String, String>() {{
        put("{customer}", "示例客户公司");
        put("{certificateNo}", "CERT-2023-001");
        put("{reportDate}", "2023-08-15");
        put("{contractNo}", "CON-2023-008");
    }};

    // 二维码图片路径
    private static final String QR_CODE_IMAGE_PATH = "C:\\Users\\19747\\Desktop\\qrcode.png";

    // 需要加粗边框的单元格配置
    private static final BorderCell[] BORDER_CELLS = {
            // 参数说明: 行号, 列号, 上边框, 下边框, 左边框, 右边框 (true表示加粗)
                new BorderCell(11, 1, false, true, false, false),  // 第6行第2列，四边加粗
            new BorderCell(11, 2, false, true, false, false),  // 第6行第2列，四边加粗
            new BorderCell(11, 3, false, true, false, false),  // 第6行第2列，四边加粗
            new BorderCell(11, 4, false, true, false, false),  // 第6行第2列，四边加粗
            new BorderCell(11, 5, false, true, false, false),  // 第6行第2列，四边加粗
            new BorderCell(11, 6, false, true, false, false),  // 第6行第2列，四边加粗

    };

    // 边框单元格配置类
    private static class BorderCell {
        int row;
        int col;
        boolean top;
        boolean bottom;
        boolean left;
        boolean right;

        public BorderCell(int row, int col, boolean top, boolean bottom, boolean left, boolean right) {
            this.row = row;
            this.col = col;
            this.top = top;
            this.bottom = bottom;
            this.left = left;
            this.right = right;
        }
    }

    public static void main(String[] args) {
        String inputFilePath = "C:\\Users\\19747\\Desktop\\3333.xlsx";
        String outFilePath = "C:\\Users\\19747\\Desktop\\2222.xlsx";

        try (FileInputStream fileIn = new FileInputStream(inputFilePath);
             Workbook workbook = WorkbookFactory.create(fileIn);
             FileOutputStream fos = new FileOutputStream(outFilePath)) {

            Sheet sheet = workbook.getSheetAt(0);

//            replaceHeaderPlaceholders(sheet, workbook);
            addCustomBorders(sheet, workbook);

            workbook.write(fos);
            System.out.println("Excel处理完成，文件已保存到: " + outFilePath);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 给指定单元格添加自定义边框
     * @param sheet Excel工作表
     * @param workbook Excel工作簿
     */
    private static void addCustomBorders(Sheet sheet, Workbook workbook) {
        for (BorderCell borderCell : BORDER_CELLS) {
//
            System.out.println("borderCell = " + borderCell);
            Row row = sheet.getRow(borderCell.row);
            if (row == null) {
                row = sheet.createRow(borderCell.row);
            }

            Cell cell = row.getCell(borderCell.col);
            if (cell == null) {
                cell = row.createCell(borderCell.col);
            }

            // 获取或创建单元格样式（避免覆盖原有样式）
            CellStyle style = workbook.createCellStyle();
            CellStyle originalStyle = cell.getCellStyle();
            if (originalStyle != null) {
                style.cloneStyleFrom(originalStyle);
            }

            // 设置边框
            if (borderCell.top) {
                style.setBorderTop(BorderStyle.MEDIUM);
            }
            if (borderCell.bottom) {
                style.setBorderBottom(BorderStyle.MEDIUM);
            }
            if (borderCell.left) {
                style.setBorderLeft(BorderStyle.MEDIUM);
            }
            if (borderCell.right) {
                style.setBorderRight(BorderStyle.MEDIUM);
            }

            cell.setCellStyle(style);
        }
    }


    /**
     * 替换表头中的占位符
     * @param sheet Excel工作表
     * @param workbook Excel工作簿
     */
    private static void replaceHeaderPlaceholders(Sheet sheet, Workbook workbook) {
        // 先处理文本替换
        for (Row row : sheet) {
            for (Cell cell : row) {
                String cellValue = getCellValueAsString(cell);

                // 检查并替换所有占位符
                for (Map.Entry<String, String> entry : REPLACEMENT_MAP.entrySet()) {
                    if (cellValue.contains(entry.getKey())) {
                        String newValue = cellValue.replace(entry.getKey(), entry.getValue());
                        if (cell.getCellType() == CellType.FORMULA) {
                            cell.setCellFormula(newValue);
                        } else {
                            cell.setCellValue(newValue);
                        }
                    }
                }

                // 检查是否有二维码占位符
                if (cellValue.contains("{qrcode}")) {
                    // 获取单元格位置
                    int rowIndex = cell.getRowIndex();
                    int colIndex = cell.getColumnIndex();

                    // 清除单元格内容
                    cell.setCellValue("");

                    // 获取合并区域信息（如果有）
                    CellRangeAddress mergedRegion = getMergedRegion(sheet, rowIndex, colIndex);

                    // 插入二维码图片，适配合并单元格大小
                    insertImage(sheet, workbook, mergedRegion != null ? mergedRegion :
                            new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex));
                }
            }
        }
    }

    /**
     * 获取单元格所在的合并区域
     * @param sheet 工作表
     * @param row 行索引
     * @param column 列索引
     * @return 合并区域，如果不是合并单元格则返回null
     */
    private static CellRangeAddress getMergedRegion(Sheet sheet, int row, int column) {
        for (CellRangeAddress region : sheet.getMergedRegions()) {
            if (region.isInRange(row, column)) {
                return region;
            }
        }
        return null;
    }

    /**
     * 在指定区域插入图片
     * @param sheet 工作表
     * @param workbook 工作簿
     * @param region 要插入图片的区域
     */
    private static void insertImage(Sheet sheet, Workbook workbook, CellRangeAddress region) {
        try {
            // 读取图片文件
            byte[] imageBytes = IOUtils.toByteArray(new FileInputStream(QR_CODE_IMAGE_PATH));
            int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);

            // 创建绘图对象
            Drawing<?> drawing = sheet.createDrawingPatriarch();

            // 创建锚点，设置图片位置
            CreationHelper helper = workbook.getCreationHelper();
            ClientAnchor anchor = helper.createClientAnchor();

            // 设置图片位置和大小，适配合并区域
            anchor.setCol1(region.getFirstColumn());
            anchor.setRow1(region.getFirstRow());
            anchor.setCol2(region.getLastColumn() + 1); // 结束列+1
            anchor.setRow2(region.getLastRow() + 1);    // 结束行+1

            // 添加图片
            drawing.createPicture(anchor, pictureIdx);

        } catch (IOException e) {
            e.printStackTrace();
            System.err.println("无法插入二维码图片: " + e.getMessage());
        }
    }

    /**
     * 将单元格的值转换为字符串
     * @param cell 单元格
     * @return 字符串值
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }
}